/**
 * 完整数据库初始化测试脚本
 * 测试所有表是否正确创建，包括新增的通知系统表
 */

const { Pool } = require('pg')

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'aide',
  user: 'postgres',
  password: process.env.POSTGRES_PASSWORD || 'unidt@123',
})

async function testDatabaseInit() {
  const client = await pool.connect()
  
  try {
    console.log('🔍 测试完整数据库初始化...')
    
    // 预期的所有表
    const expectedTables = [
      'users',
      'verification_codes',
      'user_sessions',
      'tasks',
      'subtasks',
      'recurring_tasks',
      'recurring_task_instances',
      'kanban_columns',
      'notifications',
      'user_settings',
      'notification_settings',     // 新增
      'notification_history',      // 新增
      'notification_queue',        // 新增
      'user_behaviors',
      'suggestion_feedback'
    ]
    
    // 检查所有表是否存在
    console.log('\n📋 检查表存在性:')
    const existingTables = []
    
    for (const tableName of expectedTables) {
      const result = await client.query(`
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_name = $1 AND table_schema = 'public'
      `, [tableName])
      
      if (result.rows.length > 0) {
        console.log(`✅ ${tableName}`)
        existingTables.push(tableName)
      } else {
        console.log(`❌ ${tableName} - 缺失`)
      }
    }
    
    // 检查 tasks 表的 reminder_minutes 字段
    console.log('\n🔧 检查关键字段:')
    
    const tasksColumns = await client.query(`
      SELECT column_name, data_type, column_default
      FROM information_schema.columns 
      WHERE table_name = 'tasks' AND table_schema = 'public'
      ORDER BY column_name
    `)
    
    const reminderMinutesField = tasksColumns.rows.find(row => row.column_name === 'reminder_minutes')
    if (reminderMinutesField) {
      console.log(`✅ tasks.reminder_minutes: ${reminderMinutesField.data_type}, 默认值: ${reminderMinutesField.column_default}`)
    } else {
      console.log('❌ tasks.reminder_minutes - 字段缺失')
    }
    
    // 检查通知设置表的结构
    if (existingTables.includes('notification_settings')) {
      const notificationColumns = await client.query(`
        SELECT column_name, data_type, column_default
        FROM information_schema.columns 
        WHERE table_name = 'notification_settings' AND table_schema = 'public'
        ORDER BY column_name
      `)
      
      console.log(`✅ notification_settings 表结构 (${notificationColumns.rows.length} 字段):`)
      notificationColumns.rows.forEach(col => {
        console.log(`   - ${col.column_name}: ${col.data_type}`)
      })
    }
    
    // 检查索引
    console.log('\n📊 检查关键索引:')
    
    const expectedIndexes = [
      'idx_tasks_user_id',
      'idx_tasks_due_date',
      'idx_notification_settings_user_id',    // 新增
      'idx_notification_history_user_id',     // 新增
      'idx_notification_queue_user_id'        // 新增
    ]
    
    for (const indexName of expectedIndexes) {
      const result = await client.query(`
        SELECT indexname 
        FROM pg_indexes 
        WHERE indexname = $1 AND schemaname = 'public'
      `, [indexName])
      
      if (result.rows.length > 0) {
        console.log(`✅ ${indexName}`)
      } else {
        console.log(`❌ ${indexName} - 索引缺失`)
      }
    }
    
    // 检查管理员用户
    console.log('\n👤 检查管理员用户:')
    
    const adminUser = await client.query(`
      SELECT id, email, username, is_verified
      FROM users 
      WHERE email = 'admin@aide.com'
    `)
    
    if (adminUser.rows.length > 0) {
      const admin = adminUser.rows[0]
      console.log(`✅ 管理员用户存在: ${admin.email} (${admin.username})`)
      
      // 检查管理员是否有通知设置
      const adminNotificationSettings = await client.query(`
        SELECT * FROM notification_settings WHERE user_id = $1
      `, [admin.id])
      
      if (adminNotificationSettings.rows.length > 0) {
        console.log(`✅ 管理员通知设置已创建`)
      } else {
        console.log(`❌ 管理员通知设置缺失`)
      }
    } else {
      console.log(`❌ 管理员用户不存在`)
    }
    
    // 统计信息
    console.log('\n📈 数据库统计:')
    
    const stats = {}
    for (const tableName of existingTables) {
      const result = await client.query(`SELECT COUNT(*) FROM ${tableName}`)
      stats[tableName] = parseInt(result.rows[0].count)
    }
    
    Object.entries(stats).forEach(([table, count]) => {
      console.log(`📊 ${table}: ${count} 条记录`)
    })
    
    // 总结
    console.log('\n🎯 测试总结:')
    console.log(`✅ 表创建: ${existingTables.length}/${expectedTables.length}`)
    console.log(`✅ 关键字段: reminder_minutes ${reminderMinutesField ? '存在' : '缺失'}`)
    console.log(`✅ 通知系统: ${existingTables.includes('notification_settings') && existingTables.includes('notification_history') && existingTables.includes('notification_queue') ? '完整' : '不完整'}`)
    
    if (existingTables.length === expectedTables.length && reminderMinutesField) {
      console.log('\n🎉 数据库初始化完整性测试通过！')
      return true
    } else {
      console.log('\n⚠️ 数据库初始化存在问题，请检查')
      return false
    }
    
  } catch (error) {
    console.error('❌ 测试失败:', error.message)
    return false
  } finally {
    client.release()
  }
}

// 运行测试
testDatabaseInit()
  .then((success) => {
    if (success) {
      console.log('\n✅ 所有测试通过，数据库初始化脚本完整')
    } else {
      console.log('\n❌ 测试失败，需要修复数据库初始化脚本')
    }
    process.exit(success ? 0 : 1)
  })
  .catch((error) => {
    console.error('测试执行失败:', error)
    process.exit(1)
  })
